package ICM.server;

import java.io.BufferedOutputStream;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Hashtable;
import java.util.UUID;
import java.util.Calendar;


import common.*;

import java.util.Calendar;
import java.util.Date;
import java.util.Properties;


import javax.jws.Oneway;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import org.omg.CORBA.Current;
public class UserManager {	
/**
 * Send an email using Google SMTP server given the message as a String and the recipient email.
 * @param message
 * @param toAddress
 */
public static boolean sendMail(String message,String toAddress)
	{
		String MAIL_SERVER = "smtp.gmail.com";
		String USERNAME = "icmbraude2@gmail.com";
		String PASSWORD = "ds324GSa";
		String fromAddress = "icmbraude2@gmail.com";
		String subject = "Message from ICM system";
	   	
	   	try {	
	   		Properties properties = System.getProperties();
	   		properties.put("mail.smtps.host", MAIL_SERVER);
	   		properties.put("mail.smtps.auth", "true");
	
	   		Session session = Session.getInstance(properties);
	   		MimeMessage msg = new MimeMessage(session);
	
	   		msg.setFrom(new InternetAddress(fromAddress));
	   		msg.addRecipients(javax.mail.Message.RecipientType.TO, toAddress);
	   		msg.setSubject(subject);
	   		msg.setText(message);
	
	   		Transport tr = session.getTransport("smtps");
	   		tr.connect(MAIL_SERVER, USERNAME, PASSWORD);
	   		tr.sendMessage(msg, msg.getAllRecipients());
	   		tr.close();
	   		return true;
   		} catch (AddressException ex) {
   			System.out.println(ex.getMessage());
   			return false;
   		} catch (MessagingException ex) {
   			System.out.println(ex.getMessage());
   			return false;
	   	}
   	}
/**
 * A function that returns an Object of User (or NULL) to the Server class
 * @param ID
 * @param password
 * @return
 */
public static User Login(int ID, String password)
	{
		User user = null;
		DBManager db = DBManager.getInstance();
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement("SELECT * FROM users WHERE ID=? AND Password=?;");
			stmt.setInt(1, ID);
			stmt.setString(2, password);
			ResultSet rs = stmt.executeQuery();
			if(rs.next())
			{
				String role = rs.getString(5);
				if (role.equals("Manager"))
					user=new Manager();
				else if (role.equals("Teacher"))
					user = new Teacher();
				else if (role.equals("Employee"))
					user = new Employee();
				else if (role.equals("Student"))
					user = new Student();
				else user = new SystemEngineer();
				user.setDepartment(rs.getString(7));
				user.setId(ID);
				user.setPassword(password);
				user.setRole(rs.getString(5));
				user.setFirstname(rs.getString(2));
				user.setLastname(rs.getString(3));
				user.setEmail(rs.getString(6));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();	
		}
		return user;
	}
/**
 * Returns all active requests to current user
 * @param ID
 * @return
 */
public static Hashtable<Integer,String> UserRequests(int ID)
     {
    	 
    	 Hashtable<Integer,String> reqList=new Hashtable<Integer,String>(); 
    	 DBManager db = DBManager.getInstance();  
    
    	 try {   		 
				PreparedStatement stmt2 = db.getConnection().prepareStatement("SELECT * FROM requests WHERE UserID=? AND Status='Active';");
				stmt2.setInt(1, ID);
				ResultSet rs2 = stmt2.executeQuery();
			
				    while(rs2.next())
				    {
				    	reqList.put(rs2.getInt(1),rs2.getString(11));
				    }
				   rs2.close();
			 }
		  
		 catch (SQLException e) {
			e.printStackTrace();	
		}
    	 
    	 
    	 return reqList;
     } 
/**
 * Returns all information about specific request: Date, stage etc
 * @param ReqId
 * @return
 */
public static Request RequestInfo(int ReqId)
    	{
    		Request myArray=new Request();
    		DBManager db = DBManager.getInstance();  	 
    		
    		try {   		 
 				PreparedStatement stmt = db.getConnection().prepareStatement("SELECT * FROM requests WHERE idRequests=?;");
 				stmt.setInt(1,ReqId);
 				ResultSet rs2 = stmt.executeQuery();
 				if(rs2.next()){
 				myArray.setIdRequest(rs2.getInt(1));
 				myArray.setDepartment(rs2.getString(2));
 				myArray.setdateRequest(rs2.getDate(4));
 				myArray.setDescription(rs2.getString(5));
 				myArray.setComments(rs2.getString(6));
 				myArray.setReasons(rs2.getString(12));
 				}
    		}
 		  
 		 catch (SQLException e) {
 			e.printStackTrace();	
 		}
    		   		
    		return myArray;
    	}
/**
 * returns role of each engineer in request 
 * @param ReqId
 * @param StageId
 * @return
 */
public static String[] RequestInfo(int ReqId,int StageId)
{
	String[] myIntArray=new String[4];
	DBManager db = DBManager.getInstance();  	 
	
	try {   		 
			PreparedStatement stmt = db.getConnection().prepareStatement("SELECT * FROM stage WHERE requestID=? AND idStage=?;");
			stmt.setInt(1,ReqId);
			stmt.setInt(2,StageId);
			ResultSet rs2 = stmt.executeQuery();
		
			    while(rs2.next())
			    {
			    	myIntArray[0]=Integer.toString(rs2.getInt(1));
			    	myIntArray[1]=rs2.getString(5);
			    	myIntArray[2]=rs2.getString(2);
			    	myIntArray[3]=Integer.toString(rs2.getInt(6));
			    }
			   rs2.close();
		 }
	  
	 catch (SQLException e) {
		e.printStackTrace();	
	}
	   		
	return myIntArray;
}
/**
 * Get all the requests that a specific user participates
 * @param IDUser
 * @return
 */
public static ArrayList<String[]>  ResponsibleRequest(int IDUser)
    {
    	ArrayList<String[]> reqList = new ArrayList<String[]>();
		DBManager db = DBManager.getInstance();  	 
		
		try {   		 
				PreparedStatement stmt1 = db.getConnection().prepareStatement("SELECT NameOfStage,requestID,Status FROM stage WHERE IDresponsible=?;");
				PreparedStatement stmt2 = db.getConnection().prepareStatement("SELECT idRequests,Status FROM requests WHERE idSupervisor=?;");
				
				stmt1.setInt(1,IDUser);
				stmt2.setInt(1,IDUser);
				
				ResultSet rs1 = stmt1.executeQuery();
				ResultSet rs2 = stmt2.executeQuery();
				    while(rs1.next())
				    {
				    	if (rs1.getString("NameOfStage").equals("Closing"))
				    		reqList.add(new String[] { Integer.toString(rs1.getInt("requestID")), "Supervisor(closing)", rs1.getString("Status") });
				    	if (rs1.getString("NameOfStage").equals("Testing"))
				    		reqList.add(new String[] { Integer.toString(rs1.getInt("requestID")), "Tester", rs1.getString("Status") });
				    	if (rs1.getString("NameOfStage").equals("Meaningful assessment"))
				    		reqList.add(new String[] { Integer.toString(rs1.getInt("requestID")), "Assessor", rs1.getString("Status") });
				    	if (rs1.getString("NameOfStage").equals("Execution"))
				    		reqList.add(new String[] { Integer.toString(rs1.getInt("requestID")), "Conductor of performance", rs1.getString("Status") });
				    	if (rs1.getString("NameOfStage").equals("Review and decision"))
				    		reqList.add(new String[] { Integer.toString(rs1.getInt("requestID")), "Committee", rs1.getString("Status") });
				    }
				    while (rs2.next())
				    {
				    	reqList.add(new String[] { Integer.toString(rs2.getInt("idRequests")), "Supervisor", rs2.getString("Status") });
				    }
				   rs1.close(); 
				   rs2.close();
			 }
		  
		 catch (SQLException e) {
			e.printStackTrace();	
		}
    	return reqList;
    }
/**
 * Returns info about stages Meaningful assessment and Performance to Supervisor  
 * @param IDReq
 * @return
 */
public static ArrayList<String[]> getStagesForAppointment(int IDReq)
    {
    	  ArrayList<String[]> stage = new ArrayList<String[]>();
          DBManager db = DBManager.getInstance();  	 
		
		try {   		 
				PreparedStatement stmt1 = db.getConnection().prepareStatement("SELECT NameOfStage,DATEDIFF(DateEndOfStage,DateStartOfStage),IDresponsible FROM stage WHERE requestID=?;");				
				stmt1.setInt(1,IDReq);				
				ResultSet rs1 = stmt1.executeQuery();
				
				while(rs1.next())
				  {
				   	if(rs1.getString("NameOfStage").equals("Meaningful assessment"))				 
				   		stage.add(new String[] { "Meaningful assessment",Integer.toString(rs1.getInt("DATEDIFF(DateEndOfStage,DateStartOfStage)")),  Integer.toString(rs1.getInt("IDresponsible")) });	
				   	if(rs1.getString("NameOfStage").equals("Execution"))				 
				   		stage.add(new String[] { "Execution",Integer.toString(rs1.getInt("DATEDIFF(DateEndOfStage,DateStartOfStage)")), " "});
				  }
				   
				rs1.close(); 
				  
			 }
		  
		 catch (SQLException e) {
			e.printStackTrace();	
		}
    	return stage;
    }	
/**
 * Get the info of all the new and active requests
 */
public static Request[] getNewRequests()
     {
    	 int i=0;
    	 Request[] requests = new Request[0];
    	 DBManager db = DBManager.getInstance();
    	 String query = "SELECT requests.*,Email,Role FROM requests,users WHERE users.ID=requests.UserID AND Status<>'Finished' AND Status<>'Frozen' AND Status<>'Rejected';";
    	 Statement stmt;
		try {
			stmt = db.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				requests = Arrays.copyOf(requests, requests.length + 1);
				requests[i] = new Request();
				requests[i].setIdRequest(rs.getInt("idRequests"));
				requests[i].setDepartment(rs.getString("Department"));
				requests[i].setUserID(rs.getInt("UserID"));
				requests[i].setdateRequest(rs.getDate("dateRequest"));
				requests[i].setDescription(rs.getString("Description"));
				requests[i].setComments(rs.getString("Comments"));	
				requests[i].setidSupervisor(rs.getInt("idSupervisor"));
				requests[i].setDateOfStartTreatment(rs.getDate("DateOfStartTreatment"));
				requests[i].setStatus(rs.getString("Status"));
				requests[i].setEndDate(rs.getDate("EndDate"));
				requests[i].setidStage(rs.getInt("idStage"));
				requests[i].setReasons(rs.getString("Reasons"));
				requests[i].setEmail(rs.getString("Email"));
				requests[i].setRole(rs.getString("Role"));
				requests[i].setFileInfo(getFileInfo(rs.getInt("idRequests")));
				i++;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	 
    	 return requests;
     }
/**
 * Get the info of all the users
 * @return
 */
public static User[] getUsers()
     {
    	 int i=0;
    	 User[] users = new User[0];
    	 DBManager db = DBManager.getInstance();
    	 String query = "SELECT * FROM Users;";
    	 Statement stmt;
		try {
			stmt = db.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				users = Arrays.copyOf(users, users.length + 1);
				users[i] = new User();
				users[i].setId(rs.getInt("ID"));
				users[i].setPassword(rs.getString("Password"));
				users[i].setEmail(rs.getString("Email"));
				users[i].setFirstname(rs.getString("FirstName"));	
				users[i].setLastname(rs.getString("LastName"));
				users[i].setRole(rs.getString("Role"));
				users[i].setDepartment(rs.getString("Department"));
				i++;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	 
    	 return users;
     }
/**
 * Returns the stage name according the idStage
 * @param idStage
 * @return
 */
public static String getStageName(int idStage) 
	{
		DBManager db = DBManager.getInstance();
		String query = "SELECT NameOfStage FROM stage WHERE idStage=?;";
		PreparedStatement stmt;
		String StageName = null;
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setInt(1, idStage);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				StageName = rs.getString("NameOfStage");
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	 
    	 return StageName;
	}
/**
 * Get the info of all employees
 * @return
 */
public static User[] getEmployees() {
		 int i=0;
	   	 User[] employees = new User[0];
	   	 DBManager db = DBManager.getInstance();
	   	 String query = "SELECT * FROM Users WHERE Role='Employee';";
	   	 Statement stmt;
			try {
				stmt = db.getConnection().createStatement();
				ResultSet rs = stmt.executeQuery(query);
				while (rs.next()) {
					employees = Arrays.copyOf(employees, employees.length + 1);
					employees[i] = new User();
					employees[i].setId(rs.getInt("ID"));
					employees[i].setPassword(rs.getString("Password"));
					employees[i].setEmail(rs.getString("Email"));
					employees[i].setFirstname(rs.getString("FirstName"));	
					employees[i].setLastname(rs.getString("LastName"));
					employees[i].setRole(rs.getString("Role"));
					employees[i].setDepartment(rs.getString("Department"));
					i++;
				}
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	   	 
	   	 return employees;
	}
/**
 * Change a Supervisor of a requests and returns the result
 * @param idRequests
 * @param idSupervisor
 * @return
 */
public static boolean ChangeSupervisor(int idRequests, int idSupervisor) {
		DBManager db = DBManager.getInstance();
		String query = "UPDATE requests SET idSupervisor=? WHERE idRequests=?;";
		PreparedStatement stmt;
		boolean result = false;
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setInt(1, idSupervisor);
			stmt.setInt(2, idRequests);
			stmt.executeUpdate();
			result = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	 
    	 return result;
	}
/**
 * Get all committee members
 * @return
 */
public static User[] getCommittee() {
		int i=0;
   	 User[] users = new User[0];
   	 DBManager db = DBManager.getInstance();
   	 String query = "SELECT * FROM Users WHERE Role='Head of committee' OR Role='Committee member';";
   	 Statement stmt;
		try {
			stmt = db.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				users = Arrays.copyOf(users, users.length + 1);
				users[i] = new User();
				users[i].setId(rs.getInt("ID"));
				users[i].setPassword(rs.getString("Password"));
				users[i].setEmail(rs.getString("Email"));
				users[i].setFirstname(rs.getString("FirstName"));	
				users[i].setLastname(rs.getString("LastName"));
				users[i].setRole(rs.getString("Role"));
				users[i].setDepartment(rs.getString("Department"));
				i++;
				
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   	 
   	 return users;
	}
/**
 * Change a committee member, giving the new member the role of the old member and 
 * giving the old member the role of System Engineer.
 * @param OldMember
 * @param NewMember
 * @return
 */
public static boolean ChangeCommitteeMember(int OldMember, int NewMember) {
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt;
		ResultSet rs = null;
		String OldMemberRole = null;
		boolean result1 = false;
		boolean result2 = false;
		String query = "SELECT Role FROM users WHERE ID=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setInt(1, OldMember);
			rs = stmt.executeQuery();
			if (rs.next())
				OldMemberRole = rs.getString("Role");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query = "UPDATE users SET Role=? WHERE ID=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, OldMemberRole);
			stmt.setInt(2, NewMember);
			stmt.executeUpdate();
			result1 = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query = "UPDATE users SET Role=? WHERE ID=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, "System Engineer");
			stmt.setInt(2, OldMember);
			stmt.executeUpdate();
			result2 = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		if (result1 && result2)
			return true;
		else
			return false;
	}
/**
 * Add a new committee member given a current system engineer and setting to a role of 
 * 'Committee member' or 'Head of committee'.
 * @param NewMember
 * @param Role
 * @return
 */
public static boolean NewCommitteeMember(int NewMember, String Role) {
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt;
		ResultSet rs = null;
		boolean result = false;
		String query = "UPDATE users SET Role=? WHERE ID=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, Role);
			stmt.setInt(2, NewMember);
			stmt.executeUpdate();
			result = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
/**
 * Get all system engineers
 * @return
 */
public static SystemEngineer[] getSystemEngineers() {
		 int i=0;
		 SystemEngineer[] syseng = new SystemEngineer[0];
	   	 DBManager db = DBManager.getInstance();
	   	 String query = "SELECT * FROM Users WHERE Role='System Engineer';";
	   	 Statement stmt;
			try {
				stmt = db.getConnection().createStatement();
				ResultSet rs = stmt.executeQuery(query);
				while (rs.next()) {
					syseng = Arrays.copyOf(syseng, syseng.length + 1);
					syseng[i] = new SystemEngineer();
					syseng[i].setId(rs.getInt("ID"));
					syseng[i].setPassword(rs.getString("Password"));
					syseng[i].setEmail(rs.getString("Email"));
					syseng[i].setFirstname(rs.getString("FirstName"));	
					syseng[i].setLastname(rs.getString("LastName"));
					syseng[i].setRole(rs.getString("Role"));
					syseng[i].setDepartment(rs.getString("Department"));
					i++;
				}
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	   	 
	   	 return syseng;
	}
/**
 * Add a new request with an info that a user chose and return the result.
 * @param Department
 * @param UserID
 * @param Description
 * @param Comments
 * @param Reasons
 * @param fileupload
 * @return
 */
public static boolean AddRequest(String Department, int UserID, String Description, String Comments, String Reasons, FileUpload fileupload)
	{
		DBManager db = DBManager.getInstance();
		int len = 0;
		ResultSet rs1;
		int id = 0;
		InputStream fis = null;
		File file = null;
		Date currentDate = new Date(System.currentTimeMillis());
		java.sql.Date sqlDate = new java.sql.Date( currentDate.getTime() );
		String randomString = UUID.randomUUID().toString();
		try {
				PreparedStatement stmt2 = db.getConnection().prepareStatement("INSERT INTO requests (Department,UserID,dateRequest,Description,Comments,Reasons) VALUES (?,?,?,?,?,?);");
				stmt2.setString(1, Department);
				stmt2.setInt(2, UserID);
				stmt2.setDate(3, sqlDate);
				stmt2.setString(4,Description);
				stmt2.setString(5, Comments);
				stmt2.setString(6, Reasons);				
				stmt2.executeUpdate();
				String query = "Select max(idRequests) From requests;";
				stmt2 = db.getConnection().prepareStatement(query);
				rs1=stmt2.executeQuery();
				if(rs1.next())
					id=rs1.getInt(1);
				String ma="Meaningful assessment";
				String query1 = "INSERT INTO stage (idStage,NameOfStage,requestID) VALUES (?,?,?);";
				stmt2 = db.getConnection().prepareStatement(query1);
				stmt2.setInt(1,1);
				stmt2.setString(2, ma);
				stmt2.setInt(3, id);
				stmt2.executeUpdate();
				String RaD="Review and Desigion";
				String query2 = "INSERT INTO stage (idStage,NameOfStage,requestID) VALUES (?,?,?);";
				stmt2 = db.getConnection().prepareStatement(query2);
				stmt2.setInt(1,2);
				stmt2.setString(2, RaD);
				stmt2.setInt(3, id);
				stmt2.executeUpdate();
				String EX="Execution";
				String query3 = "INSERT INTO stage (idStage,NameOfStage,requestID) VALUES (?,?,?);";
				stmt2 = db.getConnection().prepareStatement(query3);
				stmt2.setInt(1,3);
				stmt2.setString(2, EX);
				stmt2.setInt(3, id);
				stmt2.executeUpdate();
				String t="Testing";
				String query4 = "INSERT INTO stage (idStage,NameOfStage,requestID) VALUES (?,?,?);";
				stmt2 = db.getConnection().prepareStatement(query4);
				stmt2.setInt(1,4);
				stmt2.setString(2, t);
				stmt2.setInt(3, id);
				stmt2.executeUpdate();
				String cl="Closing";
				String query5 = "INSERT INTO stage (idStage,NameOfStage,requestID) VALUES (?,?,?);";
				stmt2 = db.getConnection().prepareStatement(query5);
				stmt2.setInt(1,5);
				stmt2.setString(2, cl);
				stmt2.setInt(3, id);
				stmt2.executeUpdate();
				
				
				if (fileupload.getFiledata()!=null)
				{
					Statement stmt = db.getConnection().createStatement();
					ResultSet rs = stmt.executeQuery("SELECT idRequests FROM requests ORDER BY idRequests DESC LIMIT 1;");
					rs.next();
					fileupload.setRequestID(rs.getInt(1));
				    file=new File(randomString+"."+fileupload.getExtension());
				    if (file.exists())
				    	while (file.exists())
				    	{
				    		randomString = UUID.randomUUID().toString();
				    		file=new File(randomString+"."+fileupload.getExtension());
				    	}
				    
				    byte mybytearray[] = fileupload.getFiledata();
					
					// set output stream to write file in file system
				    BufferedOutputStream bos = null;
					try {
						bos = new BufferedOutputStream(new FileOutputStream(file));
					} catch (FileNotFoundException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					} 
				   
				    try {
						bos.write(mybytearray, 0 , mybytearray.length);
						bos.flush(); //close buffer
					    bos.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} //write out file
				
					len = fileupload.getSize();
					file=new File(randomString+"."+fileupload.getExtension());
					try {
						fis = new FileInputStream(file);
					} catch (FileNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}			
					stmt2 = db.getConnection().prepareStatement("INSERT INTO files (RequestID,Filename,Size,Filedata,Extension) VALUES (?,?,?,?,?);");
					stmt2.setInt(1, fileupload.getRequestID());
					stmt2.setString(2, fileupload.getFilename());
					stmt2.setLong(3, fileupload.getSize());
					stmt2.setBinaryStream(4, fis, len);
					stmt2.setString(5, fileupload.getExtension());
					stmt2.executeUpdate();
				}
			} 
		catch (SQLException e) 
			{
			e.printStackTrace();
			return false;
			}
		finally
		{
			if (file!=null)
				file.delete();
		}
		return true;
	
}
/**
 * Get all the requests.
 * @return
 */
public static Request[] getRequests() {
	 int i=0;
   	 Request[] requests = new Request[0];
   	 DBManager db = DBManager.getInstance();
   	 String query = "SELECT requests.*,Email,Role FROM requests,users WHERE users.ID=requests.UserID;";
   	 Statement stmt;
		try {
			stmt = db.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				requests = Arrays.copyOf(requests, requests.length + 1);
				requests[i] = new Request();
				requests[i].setIdRequest(rs.getInt("idRequests"));
				requests[i].setDepartment(rs.getString("Department"));
				requests[i].setUserID(rs.getInt("UserID"));
				requests[i].setdateRequest(rs.getDate("dateRequest"));
				requests[i].setDescription(rs.getString("Description"));
				requests[i].setComments(rs.getString("Comments"));	
				requests[i].setidSupervisor(rs.getInt("idSupervisor"));
				requests[i].setDateOfStartTreatment(rs.getDate("DateOfStartTreatment"));
				requests[i].setStatus(rs.getString("Status"));
				requests[i].setEndDate(rs.getDate("EndDate"));
				requests[i].setidStage(rs.getInt("idStage"));
				requests[i].setReasons(rs.getString("Reasons"));
				requests[i].setEmail(rs.getString("Email"));
				requests[i].setRole(rs.getString("Role"));
				i++;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   	 
   	 return requests;
}
public static boolean setStageData(int idrq,String arg0, String arg1, String arg2, String arg3,String status) {
		
		 DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 String[] StageData = new String[4];
		 boolean result = false;
		 try{
			if(status.equals("Processing"))
			{
			 	String query2 = "UPDATE stage SET DateStartOfStage=NOW(),DateEndOfStage=DATE_ADD(DateStartOfStage,INTERVAL ? DAY),IDresponsible=? WHERE requestID=? AND NameOfStage='Meaningful assessment';";
			 	String query1 = "UPDATE stage SET IDresponsible=? WHERE requestID=? AND NameOfStage='Execution';";
				
						stmt1 = db.getConnection().prepareStatement(query1);
						stmt2 = db.getConnection().prepareStatement(query2);
						//stmt1.setInt(1,Integer.parseInt(arg2));
						stmt2.setInt(1,Integer.parseInt(arg3));
						stmt1.setInt(1, Integer.parseInt(arg0));
						stmt2.setInt(2, Integer.parseInt(arg1));
						stmt1.setInt(2, idrq);
						stmt2.setInt(3,idrq);
						stmt1.executeUpdate();
						stmt2.executeUpdate();
						int num=1;
						String query = "Update stage Set Status='Active' Where idStage='"+num+"' and requestID='"+idrq+"' AND Status='Processing';";
						stmt2 = db.getConnection().prepareStatement(query);
						stmt2.executeUpdate();
						String query3 = "Update requests Set Status='Active' Where idRequests='"+idrq+"';";
						stmt2 = db.getConnection().prepareStatement(query3);
						stmt2.executeUpdate();
				
			}
			else if(status.equals("Active"))
				{
					int num=1;
					String query3 = "Update stage Set Status='Working' Where idStage='"+num+"' and requestID='"+idrq+"';";
					stmt2 = db.getConnection().prepareStatement(query3);
					stmt2.executeUpdate();
				}
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 return result;
	}
/**
 * Generate a periodic report and return it to the user.
 * @return
 */
public static Report getPeriodicReports() {
	 int i=0;
   	 Report PeriodicReport = new Report();
   	 DBManager db = DBManager.getInstance();
   	 String query;
   	 Statement stmt;
   	 PreparedStatement stmt2;
     try {
    	 query = "SELECT COUNT(*) as NumOfActiveRequests FROM requests WHERE Status='Active';";
		 stmt = db.getConnection().createStatement();
		 ResultSet rs = stmt.executeQuery(query);
		 rs.next();
		 PeriodicReport.setNumOfActiveRequests(rs.getInt(1));

		 
		 query = "SELECT COUNT(*) as NumOfClosedRequests FROM requests WHERE Status='Closed';";
		 stmt = db.getConnection().createStatement();
		 rs = stmt.executeQuery(query);
		 rs.next();
		 PeriodicReport.setNumOfClosedRequests(rs.getInt(1));
		 
		 query = "SELECT COUNT(*) as FrozenRequests FROM requests WHERE Status='Frozen';";
		 stmt = db.getConnection().createStatement();
		 rs = stmt.executeQuery(query);
		 rs.next();
		 PeriodicReport.setFrozenRequests(rs.getInt(1));
		 
		 query = "SELECT COUNT(*) as RejectedRequests FROM requests WHERE Status='Rejected';";
		 stmt = db.getConnection().createStatement();
		 rs = stmt.executeQuery(query);
		 rs.next();
		 PeriodicReport.setRejectedRequests(rs.getInt(1));
		 
		 query = "SELECT SUM(DATEDIFF(DateEndOfStage,DateStartOfStage)) as SumOfWorkingDays FROM stage;";
		 stmt = db.getConnection().createStatement();
		 rs = stmt.executeQuery(query);
		 rs.next();
		 PeriodicReport.setSumOfWorkingDays(rs.getInt(1));
		 
		 Date currentDate = new Date(System.currentTimeMillis());
		 java.sql.Date sqlDate = new java.sql.Date( currentDate.getTime() );
		 PeriodicReport.setReportDate(sqlDate);
		 
		 query = "INSERT INTO reports (NumOfActiveRequests,NumOfClosedRequests,FrozenRequests,RejectedRequests,SumOfWorkingDays,ReportDate) VALUES(?,?,?,?,?,?);";
		 stmt2 = db.getConnection().prepareStatement(query);
		 stmt2.setInt(1, PeriodicReport.getNumOfActiveRequests());
		 stmt2.setInt(2, PeriodicReport.getNumOfClosedRequests());
		 stmt2.setInt(3, PeriodicReport.getFrozenRequests());
		 stmt2.setInt(4, PeriodicReport.getRejectedRequests());
		 stmt2.setInt(5, PeriodicReport.getSumOfWorkingDays());
		 stmt2.setDate(6, PeriodicReport.getReportDate());
		 stmt2.executeUpdate();
		 
		 stmt = db.getConnection().createStatement();
		 rs = stmt.executeQuery("SELECT idReports FROM reports ORDER BY idReports DESC LIMIT 1;");
		 rs.next();
		 PeriodicReport.setidReports(rs.getInt(1));
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 
     
     return PeriodicReport;
}
/**
 * Update a stage details and return the result.
 * @param idrq
 * @param arg0
 * @param arg1
 * @param arg2
 * @param arg3
 * @return
 */
/**
 * Get all generate periodic reports
 * @return
 */
public static Report[] getAllPeriodicReports() {
	 int i=0;
   	 Report[] AllPeriodicReports = new Report[0];
   	 DBManager db = DBManager.getInstance();
   	 String query = "SELECT * FROM reports";
   	 Statement stmt;
   	try {
		stmt = db.getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			AllPeriodicReports = Arrays.copyOf(AllPeriodicReports, AllPeriodicReports.length + 1);
			AllPeriodicReports[i] = new Report();
			AllPeriodicReports[i].setidReports(rs.getInt(1));
			AllPeriodicReports[i].setNumOfActiveRequests(rs.getInt(2));
			AllPeriodicReports[i].setNumOfClosedRequests(rs.getInt(3));
			AllPeriodicReports[i].setFrozenRequests(rs.getInt(4));
			AllPeriodicReports[i].setRejectedRequests(rs.getInt(5));
			AllPeriodicReports[i].setSumOfWorkingDays(rs.getInt(6));
			AllPeriodicReports[i].setReportDate(rs.getDate(7));
			i++;
		}
		rs.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   	return AllPeriodicReports;
	}
/**
 * Add an assessor report to the database and return the result.	
 * @param Aid
 * @param Place
 * @param Description
 * @param Result
 * @param ConstraintAndRisks
 * @param duration
 * @return
 */
public static boolean AddAssessorReport(int Aid,String Place,String Description,String Result,String ConstraintAndRisks){
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt2;
		String query5 = "Select AssessedrequestID From assessorreports where AssessedrequestID=?;";
		
		
		String getidStagequery = "SELECT idStage FROM requests WHERE idRequests='"+Aid+"';";

		ResultSet rs;		
		int id = 0;
		int id1=0;
		try {
			stmt2 = db.getConnection().prepareStatement(query5);
			stmt2.setInt(1,Aid);
			rs=stmt2.executeQuery();
			if(rs.next())
			{
				stmt2 = db.getConnection().prepareStatement("Update assessorreports Set AssessedrequestID=?,Place=?,Description=?,Result=?,ConstraintsAndRisks=? Where AssessedrequestID='"+Aid+"';");
				stmt2.setInt(1,Aid);
				stmt2.setString(2,Place);
				stmt2.setString(3,Description);
				stmt2.setString(4,Result);
				stmt2.setString(5,ConstraintAndRisks);
				stmt2.executeUpdate();
				stmt2 = db.getConnection().prepareStatement(getidStagequery);
				rs=stmt2.executeQuery();
				stmt2.close();
			}
			else
			{
				stmt2 = db.getConnection().prepareStatement("INSERT INTO assessorreports (AssessedrequestID,Place,Description,Result,ConstraintsAndRisks) VALUES (?,?,?,?,?);");
				stmt2.setInt(1,Aid);
				stmt2.setString(2,Place);
				stmt2.setString(3,Description);
				stmt2.setString(4,Result);
				stmt2.setString(5,ConstraintAndRisks);
				stmt2.executeUpdate();
				stmt2 = db.getConnection().prepareStatement(getidStagequery);
				rs=stmt2.executeQuery();			
				stmt2.close();
				}
			} 
			catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 	
		return true;
	}
public static boolean AddAssessorDuration(int id,String place,int duration){
	DBManager db = DBManager.getInstance();
	PreparedStatement stmt2;
	String query = "Insert Into assessorreports (AssessedrequestID,Place,DurationAssessment) Value (?,?,?)";
	
	try{
		
		stmt2 = db.getConnection().prepareStatement(query);
		stmt2.setInt(1,id);
		stmt2.setString(2,place);
		stmt2.setInt(3,duration);
		stmt2.executeUpdate();
		String query1 = "UPDATE stage SET DateStartOfStage=NOW(),DateEndOfStage=DATE_ADD(DateStartOfStage,INTERVAL ? DAY) WHERE requestID=? AND NameOfStage='Meaningful assessment';";
		stmt2 = db.getConnection().prepareStatement(query1);
		stmt2.setInt(1,duration);
		stmt2.setInt(2, id);
		stmt2.executeUpdate();
		stmt2.close();
		
	}
		catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return false;
	} 	
	return true;

}
public static boolean AssessorCommit(int id)
{
	DBManager db = DBManager.getInstance();
	PreparedStatement stmt2; 
	String query = "Update stage SET status='Ended' WHERE idStage='1' AND requestID='"+id+"';";
	String query2= "Update requests SET idStage='2' Where idRequests='"+id+"';";
	String query3= "Update stage SET DateStartOfStage=?,Status='Active' Where idStage='2' and requestID='"+id+"';";
	String query4= "Select DateStartOfStage From stage Where idStage='2' and requestID='"+id+"';";
	ResultSet rs;
	String st;
	
	try{
		
		stmt2 = db.getConnection().prepareStatement(query);
		stmt2.executeUpdate();
		stmt2 = db.getConnection().prepareStatement(query2);
		stmt2.executeUpdate();
		stmt2 = db.getConnection().prepareStatement(query4);
		rs=stmt2.executeQuery();
		if(rs.next()){
	    st=rs.getDate(1).toString();
		stmt2 = db.getConnection().prepareStatement(query3);
		//java.sql.Date systemDate=java.sql.Date.valueOf(st);
		stmt2.setString(1,st);
		stmt2.executeUpdate();
		}
		stmt2.close();
	}
	catch (SQLException e) 
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
		return false;
	} 	
	return true;
}


/**

 * Get a performances report to the user.
 * @return
 */
public static int[] getPerformances() {
  	 int[] Performances = new int[2];
  	 DBManager db = DBManager.getInstance();
  	 String query;
  	 Statement stmt;
  	try {
  		query = "SELECT SUM(DATEDIFF(ProlongationEndDate,ProlongationStartDate)) AS ProlongationsDuration FROM stage WHERE ProlongationEndDate IS NOT NULL;";
	stmt = db.getConnection().createStatement();
	ResultSet rs = stmt.executeQuery(query);
	rs.next();
	Performances[0] = rs.getInt(1);
	
	query = "SELECT SUM(DATEDIFF(ExtraEndDate,ExtraStartDate)) AS ExtraDuration FROM stage WHERE ExtraEndDate IS NOT NULL;";
	stmt = db.getConnection().createStatement();
	rs = stmt.executeQuery(query);
	rs.next();
	Performances[1] = rs.getInt(1);
	
	rs.close();
} catch (SQLException e) {
	// TODO Auto-generated catch block
		e.printStackTrace();
	}
   	return Performances;
}
/**
 * Calculate the Median and the Standard deviation according to a given query.
 * @param query
 * @return
 */
public static double[] getMedianAndStdDev(String query) {
	 int i = 0;
	 double[] MedianAndStdDev = new double[2];
	 int[] OverallExceptionDays = new int[0];
	 double median = 0;
	 double mean = 0;
	 double temp = 0;
	 double variance = 0;
	 double StdDev = 0;
	 int middle = 0;
	 double sum = 0;
 	 DBManager db = DBManager.getInstance();
 	 Statement stmt;
 	try {
		stmt = db.getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next())
		{
			OverallExceptionDays = Arrays.copyOf(OverallExceptionDays, OverallExceptionDays.length + 1);
			OverallExceptionDays[i] = rs.getInt(2);
			i++;
		}
		if (OverallExceptionDays.length>0)
		{
			middle = OverallExceptionDays.length/2;
		    if (OverallExceptionDays.length%2 == 1) {
		    	median = OverallExceptionDays[middle];
		    } else {
		    	median = (OverallExceptionDays[middle-1] + OverallExceptionDays[middle]) / 2.0;
		    }
		    MedianAndStdDev[0] = median;
		    sum = 0.0;
	        for(double a : OverallExceptionDays)
	            sum += a;
	        mean= sum/OverallExceptionDays.length;
	        temp = 0;
	        for(double a :OverallExceptionDays)
	            temp += (mean-a)*(mean-a);
	        variance = temp/OverallExceptionDays.length;
	        StdDev = Math.sqrt(variance);
	        MedianAndStdDev[1] = StdDev;
		}
		
		rs.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
  	return MedianAndStdDev;
}
/**
 * Calculate the frequency distribution according to a given query.
 * @param query
 * @return
 */
public static FreqDistr[] getFreqDistr(String query)
{
	int i = 0;
	FreqDistr[] FreqDistr = new FreqDistr[0];
	DBManager db = DBManager.getInstance();
	Statement stmt;
	try {
		stmt = db.getConnection().createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next())
		{
			FreqDistr = Arrays.copyOf(FreqDistr, FreqDistr.length + 1);
			FreqDistr[i] = new FreqDistr();
			FreqDistr[i].setOverallExceptionDays(rs.getInt(1));
			FreqDistr[i].setFrequency(rs.getInt(2));
			i++;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return FreqDistr;
}
/**
 * Get the details of a file that belongs to a specific request.
 * @param RequestID
 * @return
 */
public static FileUpload getFileInfo(int RequestID) {
	byte[] fileData = null;
    byte[] fileBytes;
    FileUpload FileInfo = new FileUpload();
    String query;
    DBManager db = DBManager.getInstance();
    Statement stmt;
    String randomString = UUID.randomUUID().toString();
    String path = null;
    String Extension;
    try {
        query = "select * from files where RequestID ='"+RequestID+"'"; 
    stmt = db.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery(query);
    if (rs.next()) {
    	
        fileBytes = rs.getBytes(5);
        Extension = rs.getString(6);
        FileInfo.setFileID(rs.getInt(1));
        FileInfo.setRequestID(RequestID);
        FileInfo.setFilename(rs.getString(3));
        FileInfo.setSize(rs.getInt(4));
        FileInfo.setExtension(Extension);
        path=randomString+"."+Extension;
        File file=new File(path);
        while (file.exists())
    	{
    		randomString = UUID.randomUUID().toString();
    		path = randomString+"."+Extension;
	    		file=new File(path);
	    	}
            OutputStream targetFile=  new FileOutputStream(path);
            targetFile.write(fileBytes);
            targetFile.close();
        }
        else
        	return null;
        
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    File file=new File(path);
	FileInputStream d = null;
	try {
		d = new FileInputStream(file);
	    fileData = new byte[(int)file.length()];
	    DataInputStream dis = new DataInputStream (d);
	  
		dis.readFully(fileData);
		dis.close();
	} catch (IOException e2) {
		
		e2.printStackTrace();
	}
	finally
	{
		if (file!=null)
			file.delete();
	}
	FileInfo.setFiledata(fileData);
    return FileInfo;
}
	public static boolean BackStageRW(int idrq){
		 DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 PreparedStatement stmt3;
		 boolean result = false;
		 String query1 = "UPDATE stage SET Status='Active' WHERE requestID=? AND idStage=2;";
		 String query2 = "UPDATE stage SET Status='Working',ExtraStartDate=NOW(),ExtraEndDate=DATE_ADD(ExtraStartDate,INTERVAL 1 DAY),DateEndOfStage=DATE_ADD(ExtraStartDate,INTERVAL 1 DAY)  WHERE requestID=? AND idStage=1;";
		 String query3 = "UPDATE requests SET idStage=1 WHERE idRequests=?";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 stmt3 = db.getConnection().prepareStatement(query3);
			 
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt3.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 stmt3.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 
		 return result; 
	}
public static boolean CommitStage(int idrq){
	DBManager db = DBManager.getInstance();
	PreparedStatement stmt1;
	PreparedStatement stmt2;
	boolean result = false;
	 String query1 = "UPDATE stage SET Status='Ended',DateEndOfStage=Date(NOW()) WHERE requestID=? AND idStage=2;";
	 String query2 = "UPDATE stage SET Status='Working',DateStartOfStage=Date(NOW()) WHERE requestID=? AND idStage=3;";
	 try{
		 stmt1 = db.getConnection().prepareStatement(query1);
		 stmt2 = db.getConnection().prepareStatement(query2);
		 stmt1.setInt(1, idrq);
		 stmt2.setInt(1, idrq);
		 stmt1.executeUpdate();
		 stmt2.executeUpdate();
		 result = true;
	 }
	 catch(Exception e){
		 e.printStackTrace();
	 }
	return result;
}

public static boolean CommitStage2(int idrq){
	DBManager db = DBManager.getInstance();
	PreparedStatement stmt1;
	PreparedStatement stmt2;
	boolean result = false;
	 String query1 = "UPDATE stage SET Status='Ended',DateEndOfStage=Date(NOW()) WHERE requestID=? AND idStage=4;";
	 String query2 = "UPDATE stage SET Status='Working',DateStartOfStage=Date(NOW()) WHERE requestID=? AND idStage=5;";
	 try{
		 stmt1 = db.getConnection().prepareStatement(query1);
		 stmt2 = db.getConnection().prepareStatement(query2);
		 stmt1.setInt(1, idrq);
		 stmt2.setInt(1, idrq);
		 stmt1.executeUpdate();
		 stmt2.executeUpdate();
		 result = true;
	 }
	 catch(Exception e){
		 e.printStackTrace();
	 }
	return result;
}

public static String[] getAssessorReportData(int id){
	String[] st= new String[4];
	DBManager db = DBManager.getInstance();  	 
	
	try {   		 
			PreparedStatement stmt = db.getConnection().prepareStatement("SELECT Description,Result,ConstraintsAndRisks,DurationAssessment FROM assessorreports WHERE AssessedrequestID=? ;");
			stmt.setInt(1,id);
			
			ResultSet rs2 = stmt.executeQuery();
		
			    while(rs2.next())
			    {
			    	st[0]=rs2.getString(1);
			    	st[1]=rs2.getString(2);
			    	st[2]=rs2.getString(3);
			    	st[3]=Integer.toString(rs2.getInt(4));
			    	
			    }
			   rs2.close();
		 }
	  
	 catch (SQLException e) {
		e.printStackTrace();	
	}
	   		
	return st;
}
	public static String[] getStageData(int requestID, int idStage) {
		 String[] stage = new String[4];
	  	 DBManager db = DBManager.getInstance();
	  	 String query = "SELECT DateStartOfStage,DateEndOfStage,Status,StageReport FROM stage WHERE requestID=? AND idStage=?;";
	  	 PreparedStatement stmt;
			try {
				stmt = db.getConnection().prepareStatement(query);
				stmt.setInt(1, requestID);
				stmt.setInt(2, idStage);
				ResultSet rs = stmt.executeQuery();
				if (rs.next()) {
					stage[0] = rs.getDate(1).toString();
					stage[1] = rs.getDate(2).toString();
					stage[2] = rs.getString(3);
					stage[3] = rs.getString(4);
				}
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	  	 
	  	 return stage;
	}

	public static boolean setStageDuration(int requestID, int idStage, int Duration) {
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt;
		ResultSet rs = null;
		boolean result = false;
		String query = "UPDATE stage SET DateEndOfStage=DATE_ADD(NOW(),Interval ? DAY) WHERE requestID=? AND idStage=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setInt(1, Duration);
			stmt.setInt(2, requestID);
			stmt.setInt(3, idStage);
			stmt.executeUpdate();
			result = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public static boolean setStageReport(int requestID, int idStage, String StageReport) {
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt;
		ResultSet rs = null;
		boolean result = false;
		String query = "UPDATE stage SET StageReport=? WHERE requestID=? AND idStage=?;";
		try {
			stmt = db.getConnection().prepareStatement(query);
			stmt.setString(1, StageReport);
			stmt.setInt(2, requestID);
			stmt.setInt(3, idStage);
			stmt.executeUpdate();
			result = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public static boolean RejectStage(int idrq){
		DBManager db = DBManager.getInstance();
		PreparedStatement stmt1;
		PreparedStatement stmt2;
		PreparedStatement stmt3;
		PreparedStatement stmt4;
		PreparedStatement stmt5;
		boolean result = false;
		 String query1 = "UPDATE requests SET Status='Rejected' WHERE idRequests=?;";
		 String query2 = "UPDATE stage SET Status='Ended' WHERE requestID=? AND idStage=2;";
		 String query3 = "UPDATE stage SET Status='Ended' WHERE requestID=? AND idStage=3;";
		 String query4 = "UPDATE stage SET Status= 'Ended' WHERE requestID=? AND idStage=4;";
		 String query5 = "UPDATE stage SET Status= 'Working' WHERE requestID=? AND idStage=5;";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 stmt3 = db.getConnection().prepareStatement(query3);
			 stmt4 = db.getConnection().prepareStatement(query4);
			 stmt5 = db.getConnection().prepareStatement(query5);
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt3.setInt(1, idrq);
			 stmt4.setInt(1, idrq);
			 stmt5.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 stmt3.executeUpdate();
			 stmt4.executeUpdate();
			 stmt5.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		return result;
	}
	public static boolean BackStageT(int idrq) {
		 DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 PreparedStatement stmt3;
		 boolean result = false;
		 String query1 = "UPDATE stage SET Status='Active' WHERE requestID=? AND idStage=4;";
		 String query2 = "UPDATE stage SET Status='Working',ExtraStartDate=Date(NOW()),ExtraEndDate=DATE_ADD(ExtraStartDate,INTERVAL 1 DAY),DateEndOfStage=DATE_ADD(ExtraStartDate,INTERVAL 1 DAY)  WHERE requestID=? AND idStage=3;";
		 String query3 = "UPDATE requests SET idStage=3 WHERE idRequests=?";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 stmt3 = db.getConnection().prepareStatement(query3);
			 
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt3.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 stmt3.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 
		 return result; 
	}
	public static boolean CloseRequest(int idrq, String Status) {
		 DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 boolean result = false;
		 String query1 = "UPDATE stage SET Status='Ended' WHERE requestID=? AND idStage=5;";
		 String query2;
		 if (Status.equals("Rejected"))
			 query2 = "UPDATE requests SET EndDate=DATE(NOW()) WHERE idRequests=?";
		 else
			 query2 = "UPDATE requests SET Status='Closed',EndDate=DATE(NOW()) WHERE idRequests=?";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 
		 return result; 
	}
	public static boolean FinishExeStage(int idrq) {
		 DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 PreparedStatement stmt3;
		 boolean result = false;
		 String query1 = "UPDATE stage SET Status='Ended' WHERE requestID=? AND idStage=3;";
		 String query2 = "UPDATE stage SET Status='Working' WHERE requestID=? AND idStage=4;";
		 String query3 = "UPDATE requests SET idStage=4 WHERE idRequests=?;";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 stmt3 = db.getConnection().prepareStatement(query3);
			 
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt3.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 stmt3.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 
		 return result; 
	}
	public static boolean FinishAssessStage(int idrq) {
		DBManager db = DBManager.getInstance();
		 PreparedStatement stmt1;
		 PreparedStatement stmt2;
		 PreparedStatement stmt3;
		 boolean result = false;
		 String query1 = "UPDATE stage SET Status='Ended' WHERE requestID=? AND idStage=1;";
		 String query2 = "UPDATE stage SET Status='Working' WHERE requestID=? AND idStage=2;";
		 String query3 = "UPDATE requests SET idStage=2 WHERE idRequests=?;";
		 try{
			 stmt1 = db.getConnection().prepareStatement(query1);
			 stmt2 = db.getConnection().prepareStatement(query2);
			 stmt3 = db.getConnection().prepareStatement(query3);
			 
			 stmt1.setInt(1, idrq);
			 stmt2.setInt(1, idrq);
			 stmt3.setInt(1, idrq);
			 stmt1.executeUpdate();
			 stmt2.executeUpdate();
			 stmt3.executeUpdate();
			 result = true;
		 }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 
		 return result; 
	}
	
}